1532. The Most Recent Three Orders.sql

WITH ranked AS (
SELECT*,
 DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders_1532
)
SELECTc.nameAS customer_name,r.customer_id,r.order_id,r.order_date
FROM ranked r
INNER JOIN customers_1532 c ONr.customer_id=c.customer_id
WHERE rn<=3
ORDER BYc.name,c.customer_id,r.order_dateDESC;

---------------Without Window function---------------

SELECTo1.customer_id,o1.order_date,COUNT(o2.order_date)
FROM orders_1532 o1
INNER JOIN orders_1532 o2 ONo1.customer_id=o2.customer_idANDo1.order_date<=o2.order_date
GROUP BYo1.customer_id,o1.order_date
HAVINGCOUNT(o2.order_date)<=3
ORDER BY1,2DESC;

-- Main logic is over now we only need to apply 2 joins to bring other columns.